﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.Web;
using System.IO;
using System.Configuration;

namespace Tencent.OA.ACM.App
{
    /// <summary>
    ///  定义一个用于导出数据到文件的接口。
    /// </summary>
    public interface IExport
    {
        /// <summary>
        /// 将DataSet的数据导出到指定的文件中。
        /// </summary>
        /// <param name="fileName">导出后的文件名。</param>
        /// <param name="dsExport"></param>
        void Export(string fileName, DataSet dsExport);
        bool ExportToFile(string file, DataSet dsExport);
        DataTable HeaderRow { get; set; }
    }
    public abstract class ExportExcelBase : IExport
    {
        protected static readonly string ExportEncode = "utf-8";//ConfigurationManager.AppSettings["ExportEncode"];

        protected ExportExcelBase()
        {
        }
        public DataTable HeaderRow { get; set; }
        protected abstract StringWriter CreateExportWriter(DataSet dsExport);

        public void Export(string fileName, DataSet dsExport)
        {
            if (string.IsNullOrEmpty(fileName))
                throw new ArgumentNullException("fileName");
            if (dsExport == null)
                throw new ArgumentNullException("dsExport");

            HttpResponse response = HttpContext.Current.Response;

            response.Clear();
            response.Buffer = true;
            response.ContentType = "application/vnd.ms-excel";
            response.Charset = "GB2312";
            response.AppendHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName));
            StringWriter writer = this.CreateExportWriter(dsExport);
            if (writer != null)
            {
                response.Write(writer.ToString());
            }
            response.End();
        }
        public bool ExportToFile(string file, DataSet dsExport)
        {
            if (string.IsNullOrEmpty(file))
                throw new ArgumentNullException("fileName");
            if (dsExport == null)
                throw new ArgumentNullException("dsExport");

            StringWriter writer = this.CreateExportWriter(dsExport);
            if (writer != null)
            {
                using (StreamWriter sWriter = new StreamWriter(file, false, Encoding.UTF8))
                {
                    sWriter.Write(writer.GetStringBuilder().ToString());
                    sWriter.Flush();
                    sWriter.Close();
                }
            }
            return true;
        }
    }

    /// <summary>
    /// 以Html方式导出数据到Excel
    /// </summary>
    public class ExportExcelHtml : ExportExcelBase
    {
        public ExportExcelHtml()
        {
        }

        //public DataTable HeaderRow { get; set; }
        protected override StringWriter CreateExportWriter(DataSet dsExport)
        {
            if (((dsExport == null) || (dsExport.Tables.Count <= 0)) || (dsExport.Tables[0] == null))
            {
                return null;
            }
            DataTable table = dsExport.Tables[0];
            int count = table.Columns.Count;
            StringWriter writer = new StringWriter();
            this.RenderExcelHeader(writer);
            writer.WriteLine("<table x:str border=0 cellpadding=0 cellspacing=0 style='border-collapse:collapse;table-layout:fixed;'>");
            //输出头
            if (HeaderRow != null)
            {
                writer.WriteLine("    <tr>");
                for (int i = 0; i != count; i++)
                {
                    writer.WriteLine(string.Format("      <td class=xl24 style='border-top:none;border-left:none'>{0}</td>", HeaderRow.Rows[0][i].ToString()));
                }
                writer.WriteLine("    </tr>");
            }

            writer.WriteLine("    <tr>");
            foreach (DataColumn column in table.Columns)
            {
                writer.Write("      <td class=xl26 style='border-left:none;' ss:Type=\"String\">");
                writer.Write(column.ColumnName);
                writer.WriteLine("</td>");
            }
            writer.WriteLine("    </tr>");
            foreach (DataRow row in table.Rows)
            {
                writer.WriteLine("    <tr>");
                for (int i = 0; i != count; i++)
                {
                    writer.WriteLine(string.Format("      <td class=xl24 style='border-top:none;border-left:none'>{0}</td>", row[i].ToString()));
                }
                writer.WriteLine("    </tr>");
            }
            writer.WriteLine("\t</table>");
            this.RenderExcelFooter(writer);
            return writer;
        }

        private void CreateStylesheet(StringWriter writer)
        {
            writer.WriteLine("<style><!--");
            writer.WriteLine("table {mso-displayed-decimal-separator:\"\\.\";mso-displayed-thousand-separator:\"\\,\";}");
            writer.WriteLine("@page{margin:1.0in .75in 1.0in .75in;mso-header-margin:.5in;mso-footer-margin:.5in;}");
            writer.WriteLine("tr{mso-height-source:auto;mso-ruby-visibility:none;}");
            writer.WriteLine("col {mso-width-source:auto;mso-ruby-visibility:none;}");
            writer.WriteLine("br {mso-data-placement:same-cell;}");
            writer.WriteLine(".style0 {mso-number-format:General;text-align:general;vertical-align:bottom;white-space:nowrap;mso-rotate:0;mso-background-source:auto;mso-pattern:auto;color:windowtext;font-size:12.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:新細明體;mso-generic-font-family:auto;mso-font-charset:136;border:none;mso-protection:locked visible;mso-style-name:一般;mso-style-id:0;}");
            writer.WriteLine("td {mso-style-parent:style0;padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:12.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:新細明體;mso-generic-font-family:auto;mso-font-charset:136;mso-number-format:General;text-align:general;vertical-align:bottom;border:none;mso-background-source:auto;mso-pattern:auto;mso-protection:locked visible;white-space:nowrap;mso-rotate:0;}");
            writer.WriteLine(".xl24 {mso-style-parent:style0;font-family:\"Times New Roman\", serif;mso-font-charset:0;border:.5pt solid windowtext;white-space:normal;}");
            writer.WriteLine(".xl25{mso-style-parent:style0;border:.5pt solid windowtext;}");
            writer.WriteLine(".xl26 {mso-style-parent:style0;font-family:\"Times New Roman\", serif;mso-font-charset:0;border:.5pt solid windowtext;background:silver;mso-pattern:auto none;}");
            writer.WriteLine("ruby {ruby-align:left;}");
            writer.WriteLine("rt {color:windowtext;font-size:9.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:新細明體, serif;mso-font-charset:136;mso-char-type:none;display:none;}");
            writer.WriteLine("--></style>");
        }

        protected virtual void RenderExcelFooter(StringWriter writer)
        {
            writer.WriteLine("\t</body>");
            writer.WriteLine("</html>");
        }

        protected virtual void RenderExcelHeader(StringWriter writer)
        {
            writer.WriteLine("<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns=\"http://www.w3.org/TR/REC-html40\">");
            writer.WriteLine("<head>");
            writer.WriteLine("<meta http-equiv=Content-Type content=\"text/html; charset=" + (string.IsNullOrEmpty(ExportExcelBase.ExportEncode) ? "utf-8" : ExportExcelBase.ExportEncode) + "\">");
            writer.WriteLine("<meta name=ProgId content=Excel.Sheet>");
            writer.WriteLine("<meta name=Generator content=\"Microsoft Excel 9\">");
            writer.WriteLine("<!--[if gte mso 9]><xml>");
            writer.WriteLine(" <o:DocumentProperties>");
            writer.WriteLine("  <o:LastAuthor>Tencent</o:LastAuthor>");
            writer.WriteLine(string.Format("  <o:LastSaved>{0}T{1}Z</o:LastSaved>", DateTime.Now.ToString("yyyy-MM-dd"), DateTime.Now.ToString("HH:mm:SS")));
            writer.WriteLine("  <o:Version>9.2812</o:Version>");
            writer.WriteLine(" </o:DocumentProperties>");
            writer.WriteLine("</xml><![endif]-->");
            this.CreateStylesheet(writer);
            writer.WriteLine("<!--[if gte mso 9]><xml>");
            writer.WriteLine(" <x:ExcelWorkbook>");
            writer.WriteLine("  <x:ExcelWorksheets>");
            writer.WriteLine("   <x:ExcelWorksheet>");
            writer.WriteLine("    <x:Name>sheet001</x:Name>");
            writer.WriteLine("    <x:WorksheetOptions>");
            writer.WriteLine("     <x:DefaultRowHeight>330</x:DefaultRowHeight>");
            writer.WriteLine("     <x:Selected/>");
            writer.WriteLine("     <x:DoNotDisplayGridlines/>");
            writer.WriteLine("     <x:Panes>");
            writer.WriteLine("      <x:Pane>");
            writer.WriteLine("       <x:Number>3</x:Number>");
            writer.WriteLine("       <x:ActiveRow>10</x:ActiveRow>");
            writer.WriteLine("       <x:ActiveCol>9</x:ActiveCol>");
            writer.WriteLine("      </x:Pane>");
            writer.WriteLine("     </x:Panes>");
            writer.WriteLine("     <x:ProtectContents>False</x:ProtectContents>");
            writer.WriteLine("     <x:ProtectObjects>False</x:ProtectObjects>");
            writer.WriteLine("     <x:ProtectScenarios>False</x:ProtectScenarios>");
            writer.WriteLine("    </x:WorksheetOptions>");
            writer.WriteLine("   </x:ExcelWorksheet>");
            writer.WriteLine("  </x:ExcelWorksheets>");
            writer.WriteLine("  <x:ProtectStructure>False</x:ProtectStructure>");
            writer.WriteLine("  <x:ProtectWindows>False</x:ProtectWindows>");
            writer.WriteLine(" </x:ExcelWorkbook>");
            writer.WriteLine("</xml><![endif]-->");
            writer.WriteLine("</head>");
            writer.WriteLine("<body link=blue vlink=purple class=xl25>");
        }
    }

    /// <summary>
    /// 以XML方式导出数据到Excel。
    /// </summary>
    public class ExportExcelXml : ExportExcelBase
    {
        public ExportExcelXml()
        {
        }

        protected override StringWriter CreateExportWriter(DataSet dsExport)
        {
            StringWriter writer = new StringWriter();
            this.RenderExcelHeader(writer);
            DataTable table = dsExport.Tables[0];

            int count = table.Columns.Count;
            int rowCount = table.Rows.Count;
            writer.WriteLine(string.Format(" <Worksheet ss:Name=\"{0}\">", table.TableName));
            int fullRows = rowCount + 1;
            if (HeaderRow != null)
                fullRows++;
            writer.WriteLine(string.Format("  <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\" x:FullRows=\"1\">", count.ToString(), fullRows.ToString()));
            if (HeaderRow != null)
            {
                writer.WriteLine("    <Row>");
                for (int i = 0; i != count; i++)
                {
                    writer.Write(string.Format("      <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">" + HeaderRow.Rows[0][i]));
                    writer.WriteLine("</Data></Cell>");
                }
                writer.WriteLine("    </Row>");
            }
            writer.WriteLine("    <Row>");
            foreach (DataColumn column in table.Columns)
            {
                writer.Write("      <Cell ss:StyleID=\"s20\"><Data ss:Type=\"String\">");
                writer.Write(column.ColumnName);
                writer.WriteLine("</Data></Cell>");
            }
            writer.WriteLine("    </Row>");
            foreach (DataRow row in table.Rows)
            {
                writer.WriteLine("    <Row>");
                for (int i = 0; i != count; i++)
                {
                    Type dataType = table.Columns[i].DataType;
                    writer.Write(string.Format("      <Cell ss:StyleID=\"s21\"><Data ss:Type=\"{0}\">", this.GetExcelTypeName(dataType)));
                    if (dataType == typeof(DateTime))
                    {
                        writer.Write(((DateTime)row[i]).ToString("yyyy-MM-dd"));
                    }
                    else
                    {
                        writer.Write(row[i].ToString());
                    }
                    writer.WriteLine("</Data></Cell>");
                }
                writer.WriteLine("    </Row>");
            }
            writer.WriteLine("  </Table>");
            writer.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
            writer.WriteLine("   <Selected/>");
            writer.WriteLine("   <Panes/>");
            writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
            writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
            writer.WriteLine("  </WorksheetOptions>");
            writer.WriteLine(" </Worksheet>");

            this.RenderExcelEnd(writer);
            return writer;
        }

        protected virtual string GetExcelTypeName(Type t)
        {
            if (((t == typeof(short)) || (t == typeof(int))) || (t == typeof(long)))
            {
                return "Number";
            }
            if (t == typeof(DateTime))
            {
                return "DateTime";
            }
            return "String";
        }

        protected virtual void RenderExcelEnd(StringWriter writer)
        {
            writer.WriteLine("</Workbook>");
        }

        protected virtual void RenderExcelHeader(StringWriter writer)
        {
            writer.WriteLine("<?xml version=\"1.0\"?>");
            writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
            writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
            writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
            writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
            writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
            writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
            writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
            writer.WriteLine("  <Author />");
            writer.WriteLine(string.Format("  <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-MM-dd"), DateTime.Now.ToString("HH:mm:SS")));
            writer.WriteLine("  <Company>Glacier Limited.</Company>");
            writer.WriteLine("  <Version>11.6408</Version>");
            writer.WriteLine(" </DocumentProperties>");
            writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");

            writer.WriteLine("  <WindowHeight>8955</WindowHeight>");
            writer.WriteLine("  <WindowWidth>11355</WindowWidth>");
            writer.WriteLine("  <WindowTopX>480</WindowTopX>");
            writer.WriteLine("  <WindowTopY>15</WindowTopY>");
            writer.WriteLine("  <ProtectStructure>False</ProtectStructure>");
            writer.WriteLine("  <ProtectWindows>False</ProtectWindows>");
            writer.WriteLine(" </ExcelWorkbook>");
            writer.WriteLine(" <Styles>");
            writer.WriteLine("  <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
            writer.WriteLine("    <Alignment/>");
            writer.WriteLine("    <Borders/>");
            writer.WriteLine("    <Font/>");
            writer.WriteLine("    <Interior/>");
            writer.WriteLine("    <Protection/>");
            writer.WriteLine("  </Style>");
            writer.WriteLine("  <Style ss:ID=\"s20\">");
            writer.WriteLine("    <Alignment ss:Vertical=\"Center\"/>");
            writer.WriteLine("    <Borders>");
            writer.WriteLine("      <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
            writer.WriteLine("      <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
            writer.WriteLine("      <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
            writer.WriteLine("      <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
            writer.WriteLine("    </Borders>");
            writer.WriteLine("    <Font ss:Bold=\"1\" ss:Size=\"11\" />");
            writer.WriteLine("    <Interior ss:Pattern=\"Solid\"/>");
            writer.WriteLine("  </Style>");
            writer.WriteLine("  <Style ss:ID=\"s21\">");
            writer.WriteLine("   <Alignment ss:WrapText=\"0\" Vertical=\"Center\" />");
            writer.WriteLine("   <Borders>");
            writer.WriteLine("     <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
            writer.WriteLine("     <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
            writer.WriteLine("     <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
            writer.WriteLine("     <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
            writer.WriteLine("   </Borders>");
            writer.WriteLine("  </Style>");
            writer.WriteLine(" </Styles>");
        }
    }

}
